import com.mysql.jdbc.Util;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;

/**
 * Created with IntelliJ IEDA.
 * Description:
 * User:86186
 * Date:2022-08-14
 * Time:13:47
 */

//增加服务
public class JDBCAdd {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("wgz20030828");

        //连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        //输入
//        Scanner scanner = new Scanner(System.in);
//        System.out.println("请输入编号:");
//        int id = scanner.nextInt();
//        System.out.println("请输入书籍编号:");
//        int book_id = scanner.nextInt();
//        System.out.println("请输入借书同学的学号:");
//        int student_id = scanner.nextInt();
//        System.out.println("请输入借书起始时间:");
//        String start_time = scanner.next();
//        System.out.println("请输入还书时间:");
//        String end_time = scanner.next();


        // ? 作为占位符. 占个位置, 后面会替换成其他的值.
        //INSERT INTO borrow_info VALUES (1, 1, 1, '2018-11-07 18:50:43', '2018-12-07 18:51:01');
        // 新增貂蝉同学的借阅记录：诗经，从2019年9月25日17:50到2019年10月25日17:50
//        String sql ="INSERT INTO borrow_info VALUES (?, ?, ?, ?, ?)";
        String sql = "insert into borrow_info(book_id, student_id,start_time, end_time) select b.id,s.id,?,? from book as b,student as s where b.name=? and s.name=?";
        //预处理
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //add
        preparedStatement.setTimestamp(1, getTimestamp("2019-09-25 17:50:00"));

        preparedStatement.setTimestamp(2, getTimestamp("2019-10-25 17:50:00"));

        preparedStatement.setString(3, "诗经");

        preparedStatement.setString(4, "貂蝉");

        //执行
        int n = preparedStatement.executeUpdate();
        //查看执行的行数
        System.out.println("n = "+ n);

        //完成之后, 就需要关闭释放相关资源.
        preparedStatement.close();
        connection.close();

    }
    //日期字符串转Java日期类Date和sql时间戳Timestamp

    public static Timestamp getTimestamp(String dateString){

        try {

            // 年-月-日 小时:分钟:秒

            java.util.Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(dateString);

            return new java.sql.Timestamp(date.getTime());

        } catch (ParseException e) {

            throw new RuntimeException("日期格式化错误："+dateString, e);

        }

    }
}
